﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using famails.common;
using famails.dto;
using famails.App_Code.source.dto;

namespace famails.dao
{
    /// <summary>
    /// Summary description for ArticleDTO
    /// </summary>
    public class CM_CustomerDAO
    {
        public CM_CustomerDAO()
        {

        }

        public DataTable GetByID(decimal ID, int UserID)
        {
            SqlCommand cmd = new SqlCommand("SP_CM_Customer_Get4Edit", ConnectionData._MyConnection);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@CustomerID", SqlDbType.Decimal).Value = ID;
            cmd.Parameters.Add("@UserID", SqlDbType.Int).Value = UserID;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable table = new DataTable();
            adapter.Fill(table);
            cmd.Dispose();
            adapter.Dispose();
            return table;
        }

        public DataTable Search(string email, string phone, string name, int groupid, int userid, string from, string to)
        {
            SqlCommand cmd = new SqlCommand("SP_CM_Customer_Search", ConnectionData._MyConnection);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@email", SqlDbType.NVarChar).Value = email;
            cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value = name;
            cmd.Parameters.Add("@phone", SqlDbType.NVarChar).Value = phone;
            cmd.Parameters.Add("@from", SqlDbType.NVarChar).Value = from;
            cmd.Parameters.Add("@to", SqlDbType.NVarChar).Value = to;
            cmd.Parameters.Add("@groupid", SqlDbType.Int).Value = groupid;
            cmd.Parameters.Add("@userid", SqlDbType.Int).Value = userid;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable table = new DataTable();
            adapter.Fill(table);
            cmd.Dispose();
            adapter.Dispose();
            return table;
        }

        public object Insert(CM_CustomerDTO dto)
        {
            SqlCommand cmd = new SqlCommand(@"insert into tblCM_Customers(CustomerName, Email, Address, Phone, Job, Company, CreateDate, Sex, BirthDay,GroupID, createby)
                                                                    values(@CustomerName, @Email, @Address, @Phone, @Job, @Company, Getdate(), @Sex, @BirthDay, @GroupID, @createby) select @@identity", ConnectionData._MyConnection);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@CreateBy", SqlDbType.Int).Value = dto.createBy;
            cmd.Parameters.Add("@CustomerName", SqlDbType.NVarChar).Value = dto.CustomerName;
            cmd.Parameters.Add("@Email", SqlDbType.NVarChar).Value = dto.Email;
            cmd.Parameters.Add("@Address", SqlDbType.NVarChar).Value = dto.Address;
            cmd.Parameters.Add("@Phone", SqlDbType.NVarChar).Value = dto.Phone;
            cmd.Parameters.Add("@GroupId", SqlDbType.Int).Value = dto.GroupID;
            cmd.Parameters.Add("@Job", SqlDbType.NVarChar).Value = dto.Job;
            cmd.Parameters.Add("@Company", SqlDbType.NVarChar).Value = dto.Company;
            cmd.Parameters.Add("@sex", SqlDbType.NVarChar).Value = dto.Sex;
            if (dto.BirthDay == null)
                cmd.Parameters.Add("@Birthday", SqlDbType.Date).Value = DBNull.Value;
            else cmd.Parameters.Add("@Birthday", SqlDbType.Date).Value = dto.BirthDay.Value;
            object i = cmd.ExecuteScalar();
            cmd.Dispose();
            return i;
        }

        public bool Edit(CM_CustomerDTO dto)
        {
            SqlCommand cmd = new SqlCommand("update tblCM_Customers set CustomerName=@CustomerName, Email=@Email, Address=@Address, Phone=@Phone, Job=@Job, Company=@Company, Sex=@Sex, BirthDay=@BirthDay, GroupID=@GroupID where CustomerID = @id", ConnectionData._MyConnection);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@CustomerName", SqlDbType.NVarChar).Value = dto.CustomerName;
            cmd.Parameters.Add("@Email", SqlDbType.NVarChar).Value = dto.Email;
            cmd.Parameters.Add("@Address", SqlDbType.NVarChar).Value = dto.Address;
            cmd.Parameters.Add("@Phone", SqlDbType.NVarChar).Value = dto.Phone;
            cmd.Parameters.Add("@GroupId", SqlDbType.Int).Value = dto.GroupID;
            cmd.Parameters.Add("@Job", SqlDbType.NVarChar).Value = dto.Job;
            cmd.Parameters.Add("@Company", SqlDbType.NVarChar).Value = dto.Company;
            cmd.Parameters.Add("@sex", SqlDbType.NVarChar).Value = dto.Sex;
            if (dto.BirthDay == null)
                cmd.Parameters.Add("@Birthday", SqlDbType.Date).Value = DBNull.Value;
            else cmd.Parameters.Add("@Birthday", SqlDbType.Date).Value = dto.BirthDay.Value;
            cmd.Parameters.Add("@ID", SqlDbType.Decimal).Value = dto.CustomerID;
            int i = cmd.ExecuteNonQuery();
            cmd.Dispose();
            return i > 0;
        }

        public bool Delete(int id)
        {
            SqlCommand cmd = new SqlCommand("update tblCM_Customers set isdelete=1 where CustomerID = @id", ConnectionData._MyConnection);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;
            int i = cmd.ExecuteNonQuery();
            cmd.Dispose();
            return i > 0;
        }

        internal bool CheckExistsEmail(string email, int id, int userid)
        {
            SqlCommand cmd = new SqlCommand(@"SELECT CustomerID FROM tblCM_Customers WHERE CustomerID <> @id and email = @email and isdelete=0
            and (createby = @UserID or createby in (select userId from tblSubClient where clientid in (select clientId from tblClient where userId=@UserID)))", ConnectionData._MyConnection);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@email", SqlDbType.NVarChar).Value = email;
            cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;
            cmd.Parameters.Add("@userid", SqlDbType.Int).Value = userid;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable table = new DataTable();
            adapter.Fill(table);
            cmd.Dispose();
            adapter.Dispose();
            return table.Rows.Count > 0;
        }




        internal bool CheckBeforDelete(int id)
        {
            SqlCommand cmd = new SqlCommand("SELECT CustomerID FROM tblCM_Orders WHERE CustomerID = @id and isdelete=0", ConnectionData._MyConnection);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable table = new DataTable();
            adapter.Fill(table);
            cmd.Dispose();
            adapter.Dispose();
            return table.Rows.Count > 0;
        }

        internal decimal CustomerCare_GetCustomerCountByGroupID(int groupid, int userid)
        {
            SqlCommand cmd = new SqlCommand("SP_CustomerCare_GetCustomerCountByGroupID "+groupid+","+userid, ConnectionData._MyConnection);
            cmd.CommandType = CommandType.Text;
            object obj = cmd.ExecuteScalar();
            cmd.Dispose();
            decimal result = 0;
            decimal.TryParse(obj+"", out result);
            return result;
        }

        internal void DeleteCustomer(string Email, int UserID)
        {
            SqlCommand cmd = new SqlCommand("SP_CM_Customer_DeleteEmailIfExists '" + Email + "'," + UserID, ConnectionData._MyConnection);
            cmd.CommandType = CommandType.Text;
            object obj = cmd.ExecuteNonQuery();
            cmd.Dispose();
        }
    }
}
